How to query a SQL database in C#?
How to query a SQL database in C#? or How to execute a database query against a database in C#?
Having used other languages where this is much simpler, I was surprised at how “not simple” this was in C#. I expected it to be a little more complex than in some scripting language such as PHP, but it was way more complex.
It is nice to run the Query and store the results in a DataTable, so that is what my example shows.
There are a few simple steps to remember.
- Create a
Stringto hold the database connection string.
(Note: If you don’t know the proper format for a connection string useSqlConnectionBuilder.) - Create a SQL connection object.
- Open the SQL connection.
- Create a
Stringto hold the query. - Create a
SqlCommandobject and pass the constructor the connection string and the query string. - Use the above
SqlCommandobject to create aSqlDataReaderobject. - Create a
DataTableobject to hold all the data returned by the query. - Use the
DataTable.Load(SqlDataReader)function to put the results of the query into aDataTable. - Do something with the data in your
DataTablehere. For example, it is common to use a foreach loop to do something with each row. - Close the SQL connection.
Here is how I do it:
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data;
using System.Data.SqlClient;
namespace CountRows
{
class Program
{
static void Main(string[] args)
{
// Create a String to hold the database connection string.
// NOTE: Put in a real database connection string here or runtime won't work
string sdwConnectionString = @"Data Source = ServerName; user id=UserName; password=P@sswd!; Initial Catalog = DatabaseName;";
// Create a connection
SqlConnection sdwDBConnection = new SqlConnection(sdwConnectionString);
// Open the connection
sdwDBConnection.Open();
// Create a String to hold the query.
string query = "SELECT * FROM MyTable";
// Create a SqlCommand object and pass the constructor the connection string and the query string.
SqlCommand queryCommand = new SqlCommand(query, sdwDBConnection);
// Use the above SqlCommand object to create a SqlDataReader object.
SqlDataReader queryCommandReader = queryCommand.ExecuteReader();
// Create a DataTable object to hold all the data returned by the query.
DataTable dataTable = new DataTable();
// Use the DataTable.Load(SqlDataReader) function to put the results of the query into a DataTable.
dataTable.Load(queryCommandReader);
// Example 1 - Print your Column Headers
String columns = string.Empty;
foreach (DataColumn column in dataTable.Columns)
{
columns += column.ColumnName + " | ";
}
Console.WriteLine(columns);
// Example 2 - Print the first 10 row of data
int topRows = 10;
for (int i = 0; i < topRows; i++)
{
String rowText = string.Empty;
foreach (DataColumn column in dataTable.Columns)
{
rowText += dataTable.Rows[i][column.ColumnName] + " | ";
}
Console.WriteLine(rowText);
}
// Close the connection
sdwDBConnection.Close();
}
}
}
So now the results are stored in a DataTable.
You can now access each row of data using the DataTable.Rows collection.
Return to ADO.NET and Database with C#
Advances in digital technologies mean that sportsbooks can update odds instantaneously through
a match or race.
My web site :: 안전놀이터
I really like it when folks get together and share opinions.
Great website, continue the good work!
Here is my page Slot5000
want to ask : can we use datatable as a "table" and we mention it in the query << select * into "target table" from datatable ??
my problem is that i have 2 database on 2 different servers . i have to extract data from database1 and put it into database2 . pleeeeeeeeeeeeeeeeease help.
[…] like this approach is working for me. Thanks […]
Washington
How to execute a sql query in c# | Rhyous
Entity Framework is your friend
Here is the cleanest way I have been able to write this. I am calling a stored procedure, but you could be calling a table if needed. I am utilizing this with jQuery Ajax returning JSON. Hope this helps some.
Notes: My connection string is stored in my Web.config for reusability and best practice.
object result = null; SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionStringNameInWebConfig"].ConnectionString); SqlCommand cmd = new SqlCommand("Stored_Proc_Name", conn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add(new SqlParameter("@ParameterNameIfNeeded", valueofparameter)); conn.Open(); SqlDataReader rdr = cmd.ExecuteReader(CommandBehavior.CloseConnection); result = rdr.Cast<IDataRecord>(); .Select(dr => new { Col_3_index_Value = dr.GetString(3), Col_7_Index_Value = dr.GetString(7) }).ToList(); // The above statement selects the desired columns by index the assigned name can be whatever you like. return result;[…] tried the following link to assist https://www.rhyous.com/2010/05/28/how-to-query-a-database-in-csharp/ and tried manually modifying to return to the method which would handle the addition of rows. This […]
[…] ? […]
so how do I return all of the rows in a table? Sometimes I only have 1 or 2 rows, other time or for other tables, I have hundreds.
Thank you,
David
THANK YOU! I have been looking way too long for this information. I am very surprised how difficult is has been to find a clean walk-through overview of how this works, without the author assuming the reader understands more that she/he does.
You've done a good service by posting this.
I want to run below script before running select statement in C# using oracle but I am getting
ORA-00900: invalid SQL statement exception ? what i am doing wrong.
conn1.Open();
using (OracleCommand myCommand = new OracleCommand
(
"exec DBMS_METADATA.SET_TRANSFORM_PARAM(DBMS_METADATA.SESSION_TRANSFORM,'SEGMENT_ATTRIBUTES', false)", conn1))
{
myCommand.ExecuteNonQuery();
}
Thank you. It is what I wanted.
regartds.
MUCH thanks. This was very helpful.
greaat code
If I want to put the results in the website how do I do it?
I assume you are using an ASP.NET application? If so, you create an ASP.NET application. Add a list control. Then have this object in your code to populate the list control.
Hello fellow,
Im thinking about "open connection", did you forget close a connection or is not needed???
Thanks you
y.-
The close connection is there on line 60.
Thanks, Rhyous. Very helpful. I am a newbie. 🙂
[...] using a DataTable in C# June 6, 2012, 12:55 pm by Rhyous You may have already read How to query a SQL database in C#? and How to insert a row into a Microsoft SQL database using C#? but you are looking for more [...]
Hi, great code, but for a beginner it's like half a story!
// Do something with each row
I just want to show one row of data for a test, but my output is blank, Im trying the following with the help of Google:-
// Do something with each row
System.Console.WriteLine(row);
Console.WriteLine(row["name"].ToString());
Console.Write("Item: ");
no luck, I know this may seem remedial to the experts, but we all start somwhere, for the sake of stress levels, please show an example where somthing gets displayed on the screen, then I know Im using it properly.
David,
I apologize for only having half the story. I try to make my articles for newbies. I will add some to this.
There now you have two examples...